<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Titongji extends Common
{
    //所属货补
    public function huobu(){
        return $this->fetch();
    }
    //所属TI预算使用报表
    public function yusuan($cid='',$page=1,$list=10){
         $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $request = Request::instance();
        $data = $request->post();
        //print_r($data);
        $where="type=1 and jine<>0 and cainian=$cid ";
        $td=[];
        $group=[];
        $fenqu=[];$sheng=[];$shi=[];$pro=[];
        if($data){
            if($data["customW"]==2){
                //分区
                $td[]="分区";
                $group[]="fenqu";
                $data["sheng"]='';$data["shi"]='';
                if($data["fenqu"]){
                    $data["fenqu"]=implode(',',explode('-',$data["fenqu"]));
                    $fenqu=db("cg_market_zoning")->where("id in ({$data["fenqu"]})")->select();
                    $where.="and fenqu in ({$data["fenqu"]}) ";
                }
            }else if($data["customW"]==3){
                //省份
                $td[]="分区";$td[]="省份";
                $group[]="fenqu";
                $group[]="sheng";
                $data["fenqu"]='';$data["shi"]='';
                if($data["sheng"]){
                    $data["sheng"]=implode(',',explode('-',$data["sheng"]));
                    $sheng=db("cg_market_provice")->where("id in ({$data["sheng"]})")->select();
                    $where.="and sheng in ({$data["sheng"]}) ";
                }
            }else if($data["customW"]==4){
                //省份
                $td[]="分区";$td[]="省份";$td[]="城市";
                 $group[]="fenqu";
                $group[]="sheng";
                $group[]="shi";
                $data["sheng"]='';$data["fenqu"]='';
                if($data["shi"]){
                    $data["shi"]=implode(',',explode('-',$data["shi"]));
                    $shi=db("cg_market_city")->where("id in ({$data["shi"]})")->select();
                    $where.="and shi in ({$data["shi"]}) ";
                }
            }

            if($data["product"]==1){
                $td[]="品牌";
                $group[]="pinpai";
                if($data["brand"]){
                    $data["brand"]=implode(',',explode('-',$data["brand"]));
                    $pro=db("pro_brands")->where("id in ({$data["brand"]})")->select();
                    $where.="and pinpai in ({$data["brand"]}) ";
                }
            }
            if($data["kemu"]==1){
                $td[]="一级费用科目";
                $group[]="yijifeiyong";
            }
            if($data["cainian"]==1){
                $td[]="一级考核期";
                $group[]="yijikaoheqi";
            }else if($data["cainian"]==2){
                $td[]="一级考核期";
                $td[]="二级考核期";
                $group[]="yijikaoheqi";
                $group[]="erjikaoheqi";
            }
        }else{
            $data=["customW"=>1,"product"=>0,"kemu"=>0,"cainian"=>0,"searchKind"=>0,"fenqu"=>'',"sheng"=>"","shi"=>"","brand"=>""];
        }
        $this->assign("data",$data);
        $this->assign("fenqu",$fenqu);
        $this->assign("sheng",$sheng);
        $this->assign("shi",$shi);
        $this->assign("pro",$pro);
        $this->assign("td",$td);
        //查询预算
        if($group){
            $gp=implode(',',$group);
            $sum=db("yusuan")->where($where)->group($gp)->count();
            $maxpage=ceil($sum/$list);
            $page=$page<$maxpage?$page:$maxpage;
            $page=$page>1?$page:1;

            $start=($page-1)*$list;
            $arr=db("yusuan")->where($where)->group($gp)->limit($start,$list)->field("$gp,sum(jine) as jine")->select();
            //print_r($arr);exit;
            
            foreach ($arr as $k => $v) {
                # code...
                $wheres="type=1 and jine<>0 and cainian=$cid ";
                foreach ($group as $kg => $vg) {
                    # code...
                    if($vg=="fenqu"){
                        if($v["fenqu"])
                            $wheres.="and fenqu={$v["fenqu"]} ";
                        else
                            $wheres.="and fenqu is null";
                    }
                    if($vg=="sheng"){
                        if($v["sheng"])
                            $wheres.="and sheng={$v["sheng"]} ";
                        else
                            $wheres.="and sheng is null ";
                    }
                    if($vg=="shi"){
                        if($v["shi"])
                            $wheres.="and shi={$v["shi"]} ";
                        else
                            $wheres.="and shi is null ";
                    }
                    if($vg=="pinpai"){
                        if($v["pinpai"])
                            $wheres.="and pinpai={$v["pinpai"]} ";
                        else
                            $wheres.="and pinpai is null ";
                    }
                    if($vg=="yijifeiyong"){
                        if($v["yijifeiyong"])
                            $wheres.="and yijifeiyong={$v["yijifeiyong"]} ";
                        else
                            $wheres.="and yijifeiyong is null ";
                    }
                    if($vg=="yijikaoheqi"){
                        if($v["yijikaoheqi"])
                            $wheres.="and yijikaoheqi={$v["yijikaoheqi"]} ";
                        else
                            $wheres.="and yijikaoheqi is null ";
                    }
                        
                    if($vg=="erjikaoheqi"){
                        if($v["erjikaoheqi"])
                            $wheres.="and erjikaoheqi={$v["erjikaoheqi"]} ";
                        else
                            $wheres.="and erjikaoheqi is null ";
                    }
                }
                //echo $wheres."<br>";
                $tmp=db("yusuan")->where($wheres)->field("id")->select();
                $arr[$k]["ids"]=[];
                foreach ($tmp as $kt => $vt) {
                    # code...
                    $arr[$k]["ids"][]=$vt["id"];
                }
                $arr[$k]["ids"]=implode(',',$arr[$k]["ids"]);
            }
        }else{
            $sum=1;
            $maxpage=ceil($sum/$list);
            $page=$page<$maxpage?$page:$maxpage;
            $page=$page>1?$page:1;

            $start=($page-1)*$list;
            $arr=db("yusuan")->where($where)->field("sum(jine) as jine")->select();
            $tmp=db("yusuan")->where($where)->field("id")->select();
                $arr[0]["ids"]=[];
                foreach ($tmp as $kt => $vt) {
                    # code...
                    $arr[0]["ids"][]=$vt["id"];
                }
                $arr[0]["ids"]=implode(',',$arr[0]["ids"]);
        }
        for ($i=0; $i <count($arr) ; $i++) { 
            # code...
            $pifu=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=2")->sum("jine");
            if($pifu)
                $arr[$i]["pifu"]=round($pifu,2);
            else
                $arr[$i]["pifu"]=0;
            $arr[$i]["weiyong"]=$arr[$i]["jine"]-$arr[$i]["pifu"];
            $sqz=db("yusuan_detail")->where("yid in ({$arr[$i]["ids"]}) and type=1")->sum("jine");
            if($sqz)
                $arr[$i]["sqz"]=round($sqz,2);
            else
                $arr[$i]["sqz"]=0;
            if($data["cainian"]==0){
                //cainian
                $tmpc=db("cg_cainian")->where("id=$cid")->find();
                $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
            }else if($data["cainian"]==1){
                $tmpc=db("cg_kaoheqi1")->where("id={$arr[$i]["yijikaoheqi"]}")->find();
                $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
            }else{
                if($arr[$i]["erjikaoheqi"]){
                    $tmpc=db("cg_kaoheqi2")->where("id={$arr[$i]["erjikaoheqi"]}")->find();
                    $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
                }else{
                    $tmpc=db("cg_kaoheqi1")->where("id={$arr[$i]["yijikaoheqi"]}")->find();
                    $arr[$i]["time"]=date("Y-m-d",$tmpc["starttime"])."至".date("Y-m-d",$tmpc["endtime"]);
                }
            }
            $index=$i+1;
            $arr[$i]["tr"]="<tr><td>{$index}</td>";
            foreach ($group as $k => $v) {
                # code...
                if($v=="fenqu"){
                    if($arr[$i]["fenqu"]){
                        $tmpfenqu=db("cg_market_zoning")->where(["id"=>$arr[$i]["fenqu"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpfenqu}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="sheng"){
                    if($arr[$i]["sheng"]){
                        $tmpsheng=db("cg_market_provice")->where(["id"=>$arr[$i]["sheng"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpsheng}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="shi"){
                    if($arr[$i]["shi"]){
                        $tmpshi=db("cg_market_city")->where(["id"=>$arr[$i]["shi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpshi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="pinpai"){
                    if($arr[$i]["pinpai"]){
                        $tmppinpai=db("pro_brands")->where(["id"=>$arr[$i]["pinpai"]])->value("brand_name");
                        $arr[$i]["tr"].="<td>{$tmppinpai}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="yijifeiyong"){
                    if($arr[$i]["yijifeiyong"]){
                        $tmpyijifeiyong=db("cg_feiyongkemu1")->where(["id"=>$arr[$i]["yijifeiyong"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijifeiyong}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="yijikaoheqi"){
                    if($arr[$i]["yijikaoheqi"]){
                        $tmpyijikaoheqi=db("cg_kaoheqi1")->where(["id"=>$arr[$i]["yijikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmpyijikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
                if($v=="erjikaoheqi"){
                    if($arr[$i]["erjikaoheqi"]){
                        $tmperjikaoheqi=db("cg_kaoheqi2")->where(["id"=>$arr[$i]["erjikaoheqi"]])->value("title");
                        $arr[$i]["tr"].="<td>{$tmperjikaoheqi}</td>";
                    }else{
                        $arr[$i]["tr"].="<td></td>";
                    }
                }
            }

            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan1'>{$arr[$i]["jine"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan2'>{$arr[$i]["pifu"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan3'>{$arr[$i]["weiyong"]}</td>";
            $arr[$i]["tr"].="<td class='ObjectNum text-right jisuan4'>{$arr[$i]["sqz"]}</td>";
            $arr[$i]["tr"].="<td>{$arr[$i]["time"]}</td>";
            if($arr[$i]["pifu"]>=$arr[$i]["jine"]){
                 $arr[$i]["tr"].="<td><div class='Redlay'>红灯</div></td>";
            }else if($arr[$i]["pifu"]+$arr[$i]["sqz"]>=$arr[$i]["jine"]){
                $arr[$i]["tr"].="<td><div class='Yellowlay'>黄灯</div></td>";
            }else{
                $arr[$i]["tr"].="<td><div class='Greenlay'>绿灯</div></td>";
            }
            $arr[$i]["tr"].="</tr>";
        }
        $this->assign("arr",$arr);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
       // print_r($arr);
        return $this->fetch();
    }
    public function hashexiaolistdc(){
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) and t.status=7 ";
            $wherejxs="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";$wherejxs="1<>1 ";
        }
         $apply=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->order("t.id")
        ->field("t.id,t.sn,th.sn as dsn,e.em_name,ap.type_id,ap.sn as apsn,ap.id as apid,apd.sn as apdsn,j.title as jxs,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1,pay.title as pay,apd.net_id,apd.brand,apd.category,apd.series,apd.sku,apd.product,apd.fb_shijiweishui,apd.hx_yihexiao,apd.hx_wsq,th.weishuihexiaojine,th.youwufapiao,th.fapiaohao,th.shizengzhishui,th.shuilv,th.shuijin,th.hanshuihexiaojine,th.shijihexiaojine,apd.start,th.beizhu,apd.tm_id")->select();

        for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=date("Y-m-d",$apply[$i]["start"]);

            if($apply[$i]["net_id"]){
                $tmp=db("cg_net")->alias("n")->join("cg_system s","n.system_id=s.id")->where(["n.id"=>$apply[$i]["net_id"]])->field("n.title as net,s.title as xt")->find();
                $apply[$i]["net"]=$tmp["net"];$apply[$i]["xt"]=$tmp["xt"];

            }else{
                $apply[$i]["net"]="";$apply[$i]["xt"]="";
            }
            if($apply[$i]["tm_id"]){
                $apply[$i]["product"]=$apply[$i]["tm_id"];
            }
            if($apply[$i]["product"]){
                $apply[$i]["sku"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("sku_id");
                $apply[$i]["product"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("pro_name");
            }
            if($apply[$i]["sku"]){
                $apply[$i]["series"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("series_id");
                $apply[$i]["sku"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("sku_name");
            }
            if($apply[$i]["series"]){
                $apply[$i]["category"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("category_id");
                $apply[$i]["series"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("series_name");
            }
            if($apply[$i]["category"]){
                $apply[$i]["brand"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("brand_id");
                $apply[$i]["category"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("category_name");
            }
            $apply[$i]["brand"]=db("pro_brands")->where(["id"=>$apply[$i]["brand"]])->value("brand_name");
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属TI费用已核销明细查询");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('3')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('4')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:AF1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属TI费用已核销明细查询");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:AF2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
         $objPHPExcel->getActiveSheet()->getStyle('4')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:AF4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:AF4')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:AF4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:AF4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

         $objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
         $objPHPExcel->getActiveSheet()->setCellValue('A3', "序号");
         $objPHPExcel->getActiveSheet()->mergeCells('B3:B4');
         $objPHPExcel->getActiveSheet()->setCellValue('B3', "申请人");
         $objPHPExcel->getActiveSheet()->mergeCells('C3:C4');
         $objPHPExcel->getActiveSheet()->setCellValue('C3', "核销面单编号");
         $objPHPExcel->getActiveSheet()->mergeCells('D3:D4');
         $objPHPExcel->getActiveSheet()->setCellValue('D3', "核销明细编号");
         $objPHPExcel->getActiveSheet()->mergeCells('E3:E4');
         $objPHPExcel->getActiveSheet()->setCellValue('E3', "申请类型");
         $objPHPExcel->getActiveSheet()->mergeCells('F3:F4');
         $objPHPExcel->getActiveSheet()->setCellValue('F3', "原申请单号");
         $objPHPExcel->getActiveSheet()->mergeCells('G3:G4');
         $objPHPExcel->getActiveSheet()->setCellValue('G3', "原申请明细编号");
         $objPHPExcel->getActiveSheet()->mergeCells('H3:H4');
         $objPHPExcel->getActiveSheet()->setCellValue('H3', "经销商名称");
         $objPHPExcel->getActiveSheet()->mergeCells('I3:I4');
         $objPHPExcel->getActiveSheet()->setCellValue('I3', "系统名称");
         $objPHPExcel->getActiveSheet()->mergeCells('J3:J4');
         $objPHPExcel->getActiveSheet()->setCellValue('J3', "网点名称");
         $objPHPExcel->getActiveSheet()->mergeCells('K3:K4');
         $objPHPExcel->getActiveSheet()->setCellValue('K3', "品牌");
         $objPHPExcel->getActiveSheet()->mergeCells('L3:L4');
         $objPHPExcel->getActiveSheet()->setCellValue('L3', "品类");
         $objPHPExcel->getActiveSheet()->mergeCells('M3:M4');
         $objPHPExcel->getActiveSheet()->setCellValue('M3', "系列");
         $objPHPExcel->getActiveSheet()->mergeCells('N3:N4');
         $objPHPExcel->getActiveSheet()->setCellValue('N3', "sku");
         $objPHPExcel->getActiveSheet()->mergeCells('O3:O4');
         $objPHPExcel->getActiveSheet()->setCellValue('O3', "单品");
         $objPHPExcel->getActiveSheet()->mergeCells('P3:P4');
         $objPHPExcel->getActiveSheet()->setCellValue('P3', "一级科目");
         $objPHPExcel->getActiveSheet()->mergeCells('Q3:Q4');
         $objPHPExcel->getActiveSheet()->setCellValue('Q3', "二级科目");
         $objPHPExcel->getActiveSheet()->mergeCells('R3:R4');
         $objPHPExcel->getActiveSheet()->setCellValue('R3', "三级科目");
         $objPHPExcel->getActiveSheet()->mergeCells('S3:S4');
         $objPHPExcel->getActiveSheet()->setCellValue('S3', "支付方式");
         $objPHPExcel->getActiveSheet()->mergeCells('T3:V3');
         $objPHPExcel->getActiveSheet()->setCellValue('T3', "实际投入");
         $objPHPExcel->getActiveSheet()->setCellValue('T4', "实际投入金额");
         $objPHPExcel->getActiveSheet()->setCellValue('U4', "已核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('V4', "未核销金额");
         $objPHPExcel->getActiveSheet()->mergeCells('W3:AD3');
         $objPHPExcel->getActiveSheet()->setCellValue('W3', "本次申请核销");
         $objPHPExcel->getActiveSheet()->setCellValue('W4', "未税核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('X4', "是否有发票");
         $objPHPExcel->getActiveSheet()->setCellValue('Y4', "发票号");
         $objPHPExcel->getActiveSheet()->setCellValue('Z4', "是否增值税");
         $objPHPExcel->getActiveSheet()->setCellValue('AA4', "税率");
         $objPHPExcel->getActiveSheet()->setCellValue('AB4', "税金");
         $objPHPExcel->getActiveSheet()->setCellValue('AC4', "含税核销金额");
         $objPHPExcel->getActiveSheet()->setCellValue('AD4', "实际投入金额");
          $objPHPExcel->getActiveSheet()->mergeCells('AE3:AE4');
         $objPHPExcel->getActiveSheet()->setCellValue('AE3', "活动开始日期");
          $objPHPExcel->getActiveSheet()->mergeCells('AF3:AF4');
         $objPHPExcel->getActiveSheet()->setCellValue('AF3', "备注");

         $h=4;
         for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["em_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["sn"]."-".$apply[$i]["dsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["type_id"]==0)
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,'非常规促销',PHPExcel_Cell_DataType::TYPE_STRING);
            else if($apply[$i]["type_id"]==1)
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,'进场费用',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,'常规促销',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["apsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["apsn"]."-".$apply[$i]["apdsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["xt"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["brand"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["category"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["series"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["sku"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["product"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["kemu1"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["pay"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$apply[$i]["fb_shijiweishui"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('U'.$h,$apply[$i]["hx_yihexiao"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('V'.$h,$apply[$i]["hx_wsq"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('W'.$h,$apply[$i]["weishuihexiaojine"],PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["youwufapiao"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('X'.$h,"有",PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('X'.$h,"无",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Y'.$h,$apply[$i]["fapiaohao"],PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["shizengzhishui"])
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('Z'.$h,"是",PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('Z'.$h,"否",PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AA'.$h,$apply[$i]["shuilv"].'%',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AB'.$h,$apply[$i]["shuijin"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AC'.$h,$apply[$i]["hanshuihexiaojine"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AD'.$h,$apply[$i]["shijihexiaojine"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AE'.$h,$apply[$i]["start"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('AF'.$h,$apply[$i]["beizhu"],PHPExcel_Cell_DataType::TYPE_STRING);
         }
          $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:AF'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AA')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AB')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AC')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AD')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AE')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('AF')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属已核销免单
    public function hashexiaolist($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) and t.status=7 ";
            $wherejxs="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";$wherejxs="1<>1 ";
        }
        if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["net"])){
                $net=trim($data["net"]);
                $where.="and n.title='{$net}' ";
            }
            if(trim($data["kemu"])){
                $kemu=trim($data["kemu"]);
                $where.="and k3.title='{$kemu}' ";
            }
            if(trim($data["ename"])){
                $ename=trim($data["ename"]);
                $where.="and e.em_name='{$ename}' ";
            }
            if(trim($data["pay"])){
                $pay=trim($data["pay"]);
                $where.="and pay.title='{$pay}' ";
            }
            if (trim($data["start"])) {
                $st = strtotime($data["start"]);
                $where .= "and t.ctime>=$st ";
            }
            if (trim($data["end"])) {
                $ed = strtotime($data["end"]) + 24 * 3600;
                $where .= "and t.ctime<$ed ";
            }
        }else{
            $data=["no"=>"","jxs"=>"","net"=>"","kemu"=>"","ename"=>"","start"=>"","end"=>"","pay"=>""];
        }
        $sum=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->order("t.id")->limit($start,$list)
        ->field("t.id,t.sn,th.sn as dsn,e.em_name,ap.type_id,ap.sn as apsn,ap.id as apid,apd.sn as apdsn,j.title as jxs,k3.title as kemu3,k2.title as kemu2,k1.title as kemu1,pay.title as pay,apd.net_id,apd.brand,apd.category,apd.series,apd.sku,apd.product,apd.fb_shijiweishui,apd.hx_yihexiao,apd.hx_wsq,th.weishuihexiaojine,th.youwufapiao,th.fapiaohao,th.shizengzhishui,th.shuilv,th.shuijin,th.hanshuihexiaojine,th.shijihexiaojine,apd.start,th.beizhu,apd.tm_id")->select();

        for($i=0;$i<count($apply);$i++){
            $apply[$i]["start"]=date("Y-m-d",$apply[$i]["start"]);

            if($apply[$i]["net_id"]){
                $tmp=db("cg_net")->alias("n")->join("cg_system s","n.system_id=s.id")->where(["n.id"=>$apply[$i]["net_id"]])->field("n.title as net,s.title as xt")->find();
                $apply[$i]["net"]=$tmp["net"];$apply[$i]["xt"]=$tmp["xt"];

            }else{
                $apply[$i]["net"]="";$apply[$i]["xt"]="";
            }
            if($apply[$i]["tm_id"]){
                $apply[$i]["product"]=$apply[$i]["tm_id"];
            }
            if($apply[$i]["product"]){
                $apply[$i]["sku"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("sku_id");
                $apply[$i]["product"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("pro_name");
            }
            if($apply[$i]["sku"]){
                $apply[$i]["series"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("series_id");
                $apply[$i]["sku"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("sku_name");
            }
            if($apply[$i]["series"]){
                $apply[$i]["category"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("category_id");
                $apply[$i]["series"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("series_name");
            }
            if($apply[$i]["category"]){
                $apply[$i]["brand"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("brand_id");
                $apply[$i]["category"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("category_name");
            }
            $apply[$i]["brand"]=db("pro_brands")->where(["id"=>$apply[$i]["brand"]])->value("brand_name");
        }
        $this->assign("apply",$apply);
         $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
         //已批准
            $tmp=db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and {$wherejxs}")->field("pizhunweishui,fb_shijiweishui")->select();
            $pizhun=0;
            foreach ($tmp as $k => $v) {
               if($v["fb_shijiweishui"]){
                $pizhun+=$v["fb_shijiweishui"];
               }else{
                $pizhun+=$v["pizhunweishui"];
               }
            }
            $pizhun=round($pizhun,2);
            $this->assign("pizhun",$pizhun);

           
            $weishui=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->sum("th.weishuihexiaojine");
        if($weishui){
            $weishui=round($weishui,2);
        }else{
            $weishui=0;
        }
        $this->assign("weishui",$weishui);
        $shuijin=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->sum("th.shuijin");
        if($shuijin){
            $shuijin=round($shuijin,2);
        }else{
            $shuijin=0;
        }
        $this->assign("shuijin",$shuijin);
        $hanshui=$weishui+$shuijin;
        $this->assign("hanshui",$hanshui);
        $shiji=Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("cg_ti_hexiao_list th","t.id=th.hx_id")->join("employee e","t.cuser=e.id")
        ->join("cg_ti ap","th.ti_id=ap.id")->join("cg_ti_list apd","th.list_id=apd.id")
        ->join("cg_feiyongkemu3 k3","apd.feetype=k3.id")->join("cg_feiyongkemu2 k2","k3.pid=k2.id")
        ->join("cg_feiyongkemu1 k1","k2.pid=k1.id")->join("cg_paymethod pay","pay.id=th.zhifufangshi")->join("cg_net n","n.id=apd.net_id","LEFT")
        ->where($where)->sum("th.shijihexiaojine");
        if($shiji){
            $shiji=round($shiji,2);
        }else{
            $shiji=0;
        }
        $this->assign("shiji",$shiji);
        return $this->fetch();
    }
    //所属核销免单查询
    public function hexiaolist($page=1,$list=20){
        $request = Request::instance();
        $data = $request->post();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if ($data["status"])
                $where .= "and t.status={$data["status"]} ";
            if(trim($data["cname"])){
                $cname=trim($data["cname"]);
                $where.="and e.em_name='{$cname}' ";
            }
            if (trim($data["start"])) {
                $st = strtotime($data["start"]);
                $where .= "and t.ctime>=$st ";
            }
            if (trim($data["end"])) {
                $ed = strtotime($data["end"]) + 24 * 3600;
                $where .= "and t.ctime<$ed ";
            }
        }else{
            $data=["jxs"=>"","fenqu"=>"","sheng"=>"","no"=>"","status"=>"","cname"=>"", "start" => "", "end" => ""];
        }
         $sum = Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")->join("employee e","e.id=t.cuser")
            ->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $applys = Db::table('cg_ti_hexiao t')->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->join("employee e","e.id=t.cuser")
            ->order("t.ctime desc")
            ->where($where)->limit($start,$list)
            ->field("t.*,j.title")->select();
            foreach($applys as $k=>$v){
            $v['cuser_title'] = Db::table('employee')->where(array('id'=>$v['cuser']))->value('em_name');
            $v["state"]=db("ti_state")->where(["id"=>$v["status"]])->value("title");
            $v["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$v["id"],"ywlx"=>18])->value("id");
            $applys[$k] = $v;
        }

       $this->assign("applys", $applys);

        $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
      //所属未核销
    public function weihexiao($page=1,$list=10){
         $request = Request::instance();
        $data = $request->post();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
         if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and j.sn='{$no}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='{$shi}' ";
            }
        }else{
            $data=["no"=>"","jxs"=>"","fenqu"=>"","sheng"=>"","shi"=>""];
        }
         $jxss=db("cg_ti t")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->where($where."and t.status in (7) and (t.status2 is null or t.status2=0) and j.isdel=0")->group("t.jxs_id")
        ->order("j.id")->field("j.title,j.id,z.title as fenqu,p.title as sheng,c.title as city,j.sn")->select();
        $apply=[];
        for($i=0;$i<count($jxss);$i++){
            //已批准
            $tmp=db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui")->select();
            $pizhun=0;
            foreach ($tmp as $k => $v) {
               if($v["fb_shijiweishui"]){
                $pizhun+=$v["fb_shijiweishui"];
               }else{
                $pizhun+=$v["pizhunweishui"];
               }
            }
            if($pizhun){
                $jxss[$i]["pizhun"]=round($pizhun,2);
            }else{
                $jxss[$i]["pizhun"]=0;
            }
            //已核销
            $yihe=db("cg_ti_hexiao")->alias("ht")
            ->where("ht.status=7 and ht.jxs_id={$jxss[$i]["id"]}")->sum("notax");
            if($yihe){
                $jxss[$i]["yihe"]=round($yihe,2);
            }else{
                $jxss[$i]["yihe"]=0;
            }
            //核销申请中
            $sq=db("cg_ti_hexiao")->alias("ht")
            ->where("ht.status in (2,3,4,5,6) and ht.jxs_id={$jxss[$i]["id"]}")->sum("notax");
            if($sq){
                $jxss[$i]["sq"]=round($sq,2);
            }else{
                $jxss[$i]["sq"]=0;
            }


            //不予核销
            $buhe=db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]}")->sum("hx_byhx");
            if($buhe){
                $jxss[$i]["buhe"]=round($buhe,2);
            }else{
                $jxss[$i]["buhe"]=0;
            }
            //未申请
            $tmp1= db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]} and fb_shijiweishui is not null")->field("fb_shijiweishui,hx_wsq,hx_yihexiao")->select();
            $wsq=0;
            for($j=0;$j<count($tmp1);$j++){
                if($tmp1[$j]["hx_yihexiao"]){
                    if($tmp1[$j]["hx_wsq"])
                        $wsq+=$tmp1[$j]["hx_wsq"];
                }else{
                    $wsq+=$tmp1[$j]["fb_shijiweishui"];
                }
            }
            
            $jxss[$i]["wsq"]=round($wsq-$jxss[$i]["sq"],2);
            if($jxss[$i]["wsq"]>0||$jxss[$i]["sq"]>0||$jxss[$i]["buhe"]>0){
                $jxss[$i]["weihe"]=$jxss[$i]["wsq"]+$jxss[$i]["sq"];
                $apply[]=$jxss[$i];
            }
        }

        $this->assign("apply",$apply);
        $this->assign("data",$data);
        //print_r($sum);
        return $this->fetch();
    }
    //所属经销商未核销详情
    public function weihelist($jid){
        $where="t.jxs_id=$jid ";
        $jxss=db("cg_ti t")->join("cg_jxs j","t.jxs_id=j.id")
        ->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id","LEFT")
        ->join("cg_feiyongkemu3 k3","td.feetype=k3.id")
        ->where($where."and t.status in (7) and (t.status2 is null or t.status2=0) and j.isdel=0")
        ->field("j.title,j.id,j.sn,t.sn as apsn,td.sn as apdsn,t.id as apid,t.type_id,n.title as net,k3.title as kemu3,td.pizhunweishui,td.fb_shijiweishui,td.id as apdid,td.hx_byhx,td.start")->select();
        $apply=[];
        for($i=0;$i<count($jxss);$i++){
            if($jxss[$i]["fb_shijiweishui"])
                $jxss[$i]["pizhun"]=round($jxss[$i]["fb_shijiweishui"],2);
            else
                $jxss[$i]["pizhun"]=round($jxss[$i]["pizhunweishui"],2);
             $yh=db("cg_ti_hexiao")->alias("ht")->join("cg_ti_hexiao_list htd","htd.hx_id=ht.id")
            ->where("ht.status in (7) and htd.list_id={$jxss[$i]["apdid"]}")->sum("htd.weishuihexiaojine");
            if($yh)
                $jxss[$i]["hx_yihexiao"]=round($yh,2);
            else
                $jxss[$i]["hx_yihexiao"]=0;
           $jxss[$i]["start"]=date("Y-m-d", $jxss[$i]["start"]);
            $jxss[$i]["weihe"]=$jxss[$i]["pizhun"]-$jxss[$i]["hx_yihexiao"];
             if($jxss[$i]["hx_byhx"])
                $jxss[$i]["hx_byhx"]=round($jxss[$i]["hx_byhx"],2);
            else
                $jxss[$i]["hx_byhx"]=0;
            if($jxss[$i]["weihe"]||$jxss[$i]["hx_byhx"])
                $apply[]=$jxss[$i];
        }
        $this->assign("apply",$apply);
        return $this->fetch();
    }
    public function buhedc(){
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
         $apply= db("cg_ti_hexiao t")->join("cg_ti_hexiao_list td","td.hx_id=t.id")
        ->join("cg_ti_list apd","td.list_id=apd.id")->join("cg_ti ap","apd.ti_id=ap.id")->join("cg_jxs j","j.id=t.jxs_id","LEFT")
        ->join("cg_net n","apd.net_id=n.id","LEFT")->join("cg_system s","n.system_id=s.id","LEFT")
        ->join("cg_fijiru cf", "apd.project=cf.id")
        ->join("cg_fijiruzd fj", "apd.jrbm=fj.id")->join("department d", "fj.title=d.id")
        ->join("employee e","e.id=ap.cuser")
        ->where($where."and t.status in (2,3,4,5,6,7) and td.yuebuhexiao=1")->order("td.id")
        ->field("t.sn,td.sn as tdsn,t.id,ap.sn as apsn,apd.sn as apdsn,ap.type_id,ap.id as apid,j.title as jxs,n.title as net,s.title as system,apd.fb_shijiweishui,apd.hx_yihexiao,apd.hx_byhx,d.de_name,e.em_name,t.ctime,td.beizhu")
        ->select();
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("所属TI费用不予核销查询");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "所属TI费用不予核销查询");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"核销面单编号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"核销明细编号");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"申请类型");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"原申请单号");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"原申请明细编号");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"经销商名称");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"系统名称");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"网点名称");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"费用明细");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"已核销金额");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"未核销金额");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"费用计入部门");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"申请人");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"核销申请日期");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"备注");
         $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["sn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sn"]."_".$apply[$i]["tdsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            if($apply[$i]["type_id"]==0)
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,'非常规促销',PHPExcel_Cell_DataType::TYPE_STRING);
            else if($apply[$i]["type_id"]==1)
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,'进场费用',PHPExcel_Cell_DataType::TYPE_STRING);
            else
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,'常规促销',PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["apsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["apsn"]."_".$apply[$i]["apdsn"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["system"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["fb_shijiweishui"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["hx_yihexiao"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["hx_byhx"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["de_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["em_name"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,date("Y-m-d",$apply[$i]["ctime"]),PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["beizhu"],PHPExcel_Cell_DataType::TYPE_STRING);

        
        }
             $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);

        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }
    //所属经销商不予核销
    public function buhe($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and t.sn='{$no}' ";
            }
            if(trim($data["apno"])){
                $apno=trim($data["apno"]);
                $where.="and ap.sn='{$apno}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["net"])){
                $net=trim($data["net"]);
                $where.="and n.title='{$net}' ";
            }
            if(trim($data["ename"])){
                $ename=trim($data["ename"]);
                $where.="and e.em_name='{$ename}' ";
            }
            if (trim($data["start"])) {
                $st = strtotime($data["start"]);
                $where .= "and t.ctime>=$st ";
            }
            if (trim($data["end"])) {
                $ed = strtotime($data["end"]) + 24 * 3600;
                $where .= "and t.ctime<$ed ";
            }
        }else{
            $data=["no"=>"","jxs"=>"","ename"=>"","start"=>"","end"=>"","net"=>"","apno"=>""];
        }
         $sum=db("cg_ti_hexiao t")->join("cg_ti_hexiao_list td","td.hx_id=t.id")
        ->join("cg_ti_list apd","td.list_id=apd.id")->join("cg_ti ap","apd.ti_id=ap.id")->join("cg_jxs j","j.id=t.jxs_id","LEFT")
        ->join("cg_net n","apd.net_id=n.id","LEFT")->join("cg_system s","n.system_id=s.id","LEFT")
        ->join("cg_fijiru cf", "apd.project=cf.id")
        ->join("cg_fijiruzd fj", "apd.jrbm=fj.id")->join("department d", "fj.title=d.id")
        ->join("employee e","e.id=ap.cuser")
        ->where($where."and t.status in (2,3,4,5,6,7) and td.yuebuhexiao=1")->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply= db("cg_ti_hexiao t")->join("cg_ti_hexiao_list td","td.hx_id=t.id")
        ->join("cg_ti_list apd","td.list_id=apd.id")->join("cg_ti ap","apd.ti_id=ap.id")->join("cg_jxs j","j.id=t.jxs_id","LEFT")
        ->join("cg_net n","apd.net_id=n.id","LEFT")->join("cg_system s","n.system_id=s.id","LEFT")
        ->join("cg_fijiru cf", "apd.project=cf.id")
        ->join("cg_fijiruzd fj", "apd.jrbm=fj.id")->join("department d", "fj.title=d.id")
        ->join("employee e","e.id=ap.cuser")
        ->where($where."and t.status in (2,3,4,5,6,7) and td.yuebuhexiao=1")->order("td.id")->limit($start,$list)
        ->field("t.sn,td.sn as tdsn,t.id,ap.sn as apsn,apd.sn as apdsn,ap.type_id,ap.id as apid,j.title as jxs,n.title as net,s.title as system,apd.fb_shijiweishui,apd.hx_yihexiao,apd.hx_byhx,d.de_name,e.em_name,t.ctime,td.beizhu")
        ->select();
        $this->assign("apply",$apply);
         $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //所属经销商核销状况查询
    public function hexiaodet($page=1,$list=10){
         $request = Request::instance();
        $data = $request->post();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="t.jxs_id in ({$str}) ";
        }else{
            $where="1<>1 ";
        }
        if($data){
            if(trim($data["no"])){
                $no=trim($data["no"]);
                $where.="and j.sn='{$no}' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and j.title='{$jxs}' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='{$shi}' ";
            }
        }else{
            $data=["no"=>"","jxs"=>"","fenqu"=>"","sheng"=>"","shi"=>""];
        }
        $sum=db("cg_ti t")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->where($where."and t.status in (7) and (t.status2 is null or t.status2=0) and j.isdel=0")->group("t.jxs_id")->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $jxss=db("cg_ti t")->join("cg_jxs j","t.jxs_id=j.id")->join("cg_market_zoning z","j.zoning=z.id")
        ->join("cg_market_provice p","j.provice=p.id")->join("cg_market_city c","j.city=c.id")
        ->where($where."and t.status in (7) and (t.status2 is null or t.status2=0) and j.isdel=0")->group("t.jxs_id")
        ->order("j.id")->limit($start,$list)->field("j.title,j.id,z.title as fenqu,p.title as sheng,c.title as city,j.sn")->select();
        for($i=0;$i<count($jxss);$i++){
            //已批准
            $tmp=db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]}")->field("pizhunweishui,fb_shijiweishui")->select();
            $pizhun=0;
            foreach ($tmp as $k => $v) {
               if($v["fb_shijiweishui"]){
                $pizhun+=$v["fb_shijiweishui"];
               }else{
                $pizhun+=$v["pizhunweishui"];
               }
            }
            if($pizhun){
                $jxss[$i]["pizhun"]=round($pizhun,2);
            }else{
                $jxss[$i]["pizhun"]=0;
            }
            //已核销
            $yihe=db("cg_ti_hexiao")->alias("ht")
            ->where("ht.status=7 and ht.jxs_id={$jxss[$i]["id"]}")->sum("notax");
            if($yihe){
                $jxss[$i]["yihe"]=round($yihe,2);
            }else{
                $jxss[$i]["yihe"]=0;
            }
            //核销申请中
            $sq=db("cg_ti_hexiao")->alias("ht")
            ->where("ht.status in (2,3,4,5,6) and ht.jxs_id={$jxss[$i]["id"]}")->sum("notax");
            if($sq){
                $jxss[$i]["sq"]=round($sq,2);
            }else{
                $jxss[$i]["sq"]=0;
            }


            //不予核销
            $buhe=db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]}")->sum("hx_byhx");
            if($buhe){
                $jxss[$i]["buhe"]=round($buhe,2);
            }else{
                $jxss[$i]["buhe"]=0;
            }
            //未申请
            $tmp1= db("cg_ti")->alias("t")->join("cg_ti_list td","td.ti_id=t.id")->where("t.status in (7) and (t.status2 is null or t.status2=0) and t.jxs_id={$jxss[$i]["id"]} and fb_shijiweishui is not null")->field("fb_shijiweishui,hx_wsq,hx_yihexiao")->select();
            $wsq=0;
            for($j=0;$j<count($tmp1);$j++){
                if($tmp1[$j]["hx_yihexiao"]){
                    if($tmp1[$j]["hx_wsq"])
                        $wsq+=$tmp1[$j]["hx_wsq"];
                }else{
                    $wsq+=$tmp1[$j]["fb_shijiweishui"];
                }
            }
            
            $jxss[$i]["wsq"]=round($wsq-$jxss[$i]["sq"],2);
        }
        //print_r($jxss);exit;
        $this->assign("jxss",$jxss);
         $this->assign("data", $data);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        return $this->fetch();
    }
    //所属ti费用效果评估
    public function tiyugu($page=1,$list=10){
        $request = Request::instance();
        $data = $request->post();
        //获取所属经销商
        $jxslist=$this->my_own_jxs();
        if($jxslist){
            $str=implode(',',$jxslist);
            $where="ap.jxs_id in ({$str}) and de.index=1 and (ap.status2=0 or ap.status2 is null) and ap.status in (2,3,4,5,6,7) ";
        }else{
            $where="1<>1 ";
        }
        if($data){
            if(isset($data["type_id"])){
                if($data["type_id"]==2||$data["type_id"]==1){
                    $where.="and ap.type_id={$data["type_id"]} ";
                }else if($data["type_id"]==0){
                    if($data["nofomaltyp"]){
                        if($data["nofomaltyp"]==1){
                            $where.="and ap.type_id={$data["type_id"]} and apd.net_id is null ";
                        }else{
                            $where.="and ap.type_id={$data["type_id"]} and apd.net_id is not null ";
                        }
                    }else{
                        $where.="and ap.type_id={$data["type_id"]} ";
                    }
                }else{
                    if($data["nofomaltyp"]){
                        if($data["nofomaltyp"]==1){
                            $where.="and ap.type_id in (1,2) or (apd.type_id=0 and apd.net_id is null) ";
                        }else{
                            $where.="and ap.type_id in (1,2) or (apd.type_id=0 and apd.net_id is not null) ";
                        }
                    }
                }
            }
            if($data["stype"]){
                if($data["stype"]==1){
                    $where.="and ap.status=7 ";
                }else{
                    $where.="and ap.status in (2,3,4,5,6) ";
                }
            }
            if(trim($data["apsn"])){
                $apsn=trim($data["apsn"]);
                $where.="and ap.sn='$apsn' ";
            }
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and jz.title='$fenqu' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and jp.title='$sheng' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and jc.title='$shi' ";
            }
            if(trim($data["pay"])){
                $pay=trim($data["pay"]);
                $where.="and cp.title='$pay' ";
            }
            if(trim($data["jsn"])){
                $jsn=trim($data["jsn"]);
                $where.="and jx.sn='$jsn' ";
            }
            if(trim($data["jxs"])){
                $jxs=trim($data["jxs"]);
                $where.="and jx.title='$jxs' ";
            }
            if(trim($data["sys"])){
                $sys=trim($data["sys"]);
                $where.="and s.title='$sys' ";
            }
            if(trim($data["net"])){
                $net=trim($data["net"]);
                $where.="and n.title='$net' ";
            }
            if(trim($data["pno"])){
                $pno=trim($data["pno"]);
                $where.="and (p1.pro_no='$pno' or p2.pro_no='$pno') ";
            }
            if(trim($data["pname"])){
                $pname=trim($data["pname"]);
                $where.="and (p1.pro_name='$pname' or p2.pro_name='$pname') ";
            }
            if(trim($data["ename"])){
                $ename=trim($data["ename"]);
                $where.="and ec.em_name='$ename' ";
            }
            if(trim($data["jrbm"])){
                $jrbm=trim($data["jrbm"]);
                $where.="and db.de_name='$jrbm' ";
            }
            if(trim($data["k1"])){
                $k1=trim($data["k1"]);
                $where.="and f1.title='$k1' ";
            }
            if(trim($data["k2"])){
                $k2=trim($data["k2"]);
                $where.="and f2.title='$k2' ";
            }
            if(trim($data["k3"])){
                $k3=trim($data["k3"]);
                $where.="and f3.title='$k3' ";
            }
            if(trim($data["jrfa"])){
                $jrfa=trim($data["jrfa"]);
                $where.="and cf.title='$jrfa' ";
            }
            if($data["lock"]){
                if($data["lock"]==1){
                    $where.="and apd.is_lock=1 ";
                }else{
                    $where.="and (apd.is_lock=0 or apd.is_lock is null) ";
                }
            }
            if($data["fb"]){
                if($data["fb"]==1){
                    $where.="and ap.sfxyfk=1 ";
                }else{
                    $where.="and (ap.sfxyfk=0 or ap.sfxyfk is null) ";
                }
            }
            if($data["fbstate"]){
                if($data["fbstate"]==1){
                    $where.="and ap.sfxyfk=1 and ap.fb=1 ";
                }else{
                    $where.="and ap.sfxyfk=1 and (ap.fb=0 or ap.fb is null) ";
                }
            }
             if($data["hc"]){
                if($data["hc"]==1){
                    $where.="and apd.sfyqhc=1 ";
                }else{
                    $where.="and (apd.sfyqhc=0 or apd.sfyqhc is null) ";
                }
            }
            if($data["payee"]){
                if($data["payee"]==1){
                    $where.="and (apd.payee=0 or apd.payee is null) ";
                }else{
                    $where.="and apd.payee=1 ";
                }
            }
            if(trim($data["sde"])){
                $sde=trim($data["sde"]);
                $where.="and d.de_name='$sde' ";
            }
            if(trim($data["c1"])){
                $c1=trim($data["c1"]);
                $where.="and c1.title='$c1' ";
            }
            if(trim($data["c2"])){
                $c2=trim($data["c2"]);
                $where.="and c2.title='$c2' ";
            }
            
        }else{
            $data=[
            "type_id"=>3,"nofomaltyp"=>0,"stype"=>0,"apsn"=>"","fenqu"=>"","sheng"=>"","shi"=>"","pay"=>"","jsn"=>"","jxs"=>"",
            "sys"=>"","pno"=>"","net"=>"","pname"=>"","ename"=>"","jrbm"=>"","k1"=>"","k2"=>"","k3"=>"","jrfa"=>"","lock"=>0,"fb"=>0,
            "fbstate"=>0,"hc"=>0,"payee"=>0,"sde"=>"","c1"=>"","c2"=>"","start_end"=>"","start_start"=>"","end_start"=>"","end_end"=>""
            ];
        }
        $apply=db::table("cg_ti ap")->join("cg_ti_list apd","apd.ti_id=ap.id")->join("employee ec", "ap.cuser=ec.id", "LEFT")
            ->join("job_employee je", "ec.id=je.employee_id", "LEFT")
            ->join("job j", "je.job_id=j.id", "LEFT")
            ->join("department_employee de", "ec.id=de.employee_id", "LEFT")
            ->join("department d", "de.department_id=d.id", "LEFT")
            ->join("cg_jxs jx", "ap.jxs_id=jx.id")
            ->join("cg_market_zoning jz", "jx.zoning=jz.id", "LEFT")
            ->join("cg_market_provice jp", "jx.provice=jp.id", "LEFT")
            ->join("cg_market_city jc", "jx.city=jc.id", "LEFT")
            ->join("cg_net n","apd.net_id=n.id","LEFT")
            ->join("cg_system s","n.system_id=s.id","LEFT")
            ->join("cg_channel_types1 c1","s.oneCType=c1.id","LEFT")
            ->join("cg_channel_types2 c2","s.twoCType=c2.id","LEFT")
            ->join("cg_ti_list apdy","apd.refer_id=apdy.id","LEFT")
            ->join("cg_ti apy","apdy.ti_id=apy.id","LEFT")
            ->join("hecha_list hl","apd.id=hl.list_id","LEFT")
            ->join("hecha h","hl.he_id=h.id","LEFT")
            ->join("cg_feiyongkemu3 f3", "apd.feetype=f3.id")
            ->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
            ->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
            ->join("cg_paymethod cp", "apd.paymethod=cp.id")
            ->join("cg_fijiru cf", "apd.project=cf.id")
            ->join("cg_fijiruzd fj", "apd.jrbm=fj.id")->join("department db", "fj.title=db.id")
            ->join("pro_products p1","apd.product=p1.id","LEFT")
            ->join("pro_products p2","apd.tm_id=p2.id","LEFT")
            ->join("gys g","apd.gys=g.id","LEFT")
            ->where($where)->order("ap.id,apd.id")->limit(0,10)
            ->field("ap.id as apid,apd.id as apdid,ap.sn as apsn,apd.sn as apdsn,ap.status,ec.em_name,d.de_name,ap.type_id,ap.title,jz.title as fenqu,jp.title as sheng,jc.title as shi,jx.title as jxs,jx.sn as jsn,n.title as net,n.sn as netsn,s.title as sys,s.sn as syssn,c1.title as ch1,c2.title as ch2,apd.tm_id,apd.product,apd.sku,apd.series,apd.category,apd.brand,apd.wangdian_price,apd.huodong_price,apd.ctime,apd.start,apd.end,apd.expCanTime,apdy.sn as apdysn,apy.sn as apysn,apdy.start as ystart,apdy.end as yend,apd.sfyqhc,hl.state as hstate,hl.result,db.de_name as jrbm,cp.title as zffs,cf.title as jrfa,apd.is_lock,apd.payee,g.gys_name,f1.title as ke1,f2.title as ke2,f3.title as ke3,apd.pizhunweishui,apd.fb_shijiweishui,apd.shuilv,apd.isVAT,apd.fb_hanshui,apd.yjsjtrfy,apd.fb_shijitourufeiyong,ap.sfxyfk,ap.fb,apd.hx_status,apd.hx_byhx,apd.boxnum,apd.totalExpectedSales,apd.jizhunboxnum,apd.totalBenchmarkSales,apd.fb_boxnum,apd.fb_shijixiaoliang,ap.brief as beizhu")
            ->select();
            for($i=0;$i<count($apply);$i++){
                 if($apply[$i]["tm_id"]){
                    $apply[$i]["product"]=$apply[$i]["tm_id"];
                }
                $apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
                $apply[$i]["zeng"]=$apply[$i]["totalExpectedSales"]-$apply[$i]["totalBenchmarkSales"];
                $apply[$i]["fb_zeng"]=$apply[$i]["fb_shijixiaoliang"]-$apply[$i]["totalExpectedSales"];
                $apply[$i]["dacheng"]=$apply[$i]["fb_shijixiaoliang"]/$apply[$i]["totalExpectedSales"]*100;
                
                if($apply[$i]["product"]){
                    $apply[$i]["sku"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("sku_id");
                     $apply[$i]["pro_no"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("pro_no");
                      $apply[$i]["box_num"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("box_num");  
                     $apply[$i]["factory"]=db("pro_price")->where(["pid"=>$apply[$i]["product"]])->value("factory");
                    $apply[$i]["product"]=db("pro_products")->where(["id"=>$apply[$i]["product"]])->value("pro_name");
                    if($apply[$i]["boxnum"]){
                        $apply[$i]["boxnum"]=$apply[$i]["box_num"]*$apply[$i]["boxnum"];
                    }
                   if($apply[$i]["fb_boxnum"]){
                        $apply[$i]["fb_boxnum"]=$apply[$i]["box_num"]*$apply[$i]["fb_boxnum"];
                    }
                }else{
                    $apply[$i]["pro_no"]="";$apply[$i]["factory"]="";
                }
                if($apply[$i]["sku"]){
                    $apply[$i]["series"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("series_id");
                    $apply[$i]["sku"]=db("pro_sku")->where(["id"=>$apply[$i]["sku"]])->value("sku_name");
                }
                if($apply[$i]["series"]){
                    $apply[$i]["category"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("category_id");
                    $apply[$i]["series"]=db("pro_series")->where(["id"=>$apply[$i]["series"]])->value("series_name");
                }
                if($apply[$i]["category"]){
                    $apply[$i]["brand"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("brand_id");
                    $apply[$i]["category"]=db("pro_category")->where(["id"=>$apply[$i]["category"]])->value("category_name");
                }
                $apply[$i]["profit"]=db("pro_brands")->where(["id"=>$apply[$i]["brand"]])->value("profit");
                $apply[$i]["brand"]=db("pro_brands")->where(["id"=>$apply[$i]["brand"]])->value("brand_name");
                
                $yihenu = Db::table("cg_ti_hexiao_list thd")->join("cg_ti_hexiao th","thd.hx_id=th.id")->where("thd.list_id={$apply[$i]["apdid"]} and th.status in (1,2,3,4,5,6,7)")->group("th.id")->field("th.id,th.sn")->select();
                $yihejine=Db::table("cg_ti_hexiao_list thd")->join("cg_ti_hexiao th","thd.hx_id=th.id")->where("thd.list_id={$apply[$i]["apdid"]} and th.status in (7)")->sum("thd.weishuihexiaojine");
                if($yihejine){
                    $apply[$i]["yh"]=round($yihejine,2);
                }else{
                    $apply[$i]["yh"]=0;
                }
                $sqz=Db::table("cg_ti_hexiao_list thd")->join("cg_ti_hexiao th","thd.hx_id=th.id")->where("thd.list_id={$apply[$i]["apdid"]} and th.status in (1,2,3,4,5,6)")->sum("thd.weishuihexiaojine");
                if($sqz){
                    $apply[$i]["sqz"]=round($sqz,2);
                }else{
                    $apply[$i]["sqz"]=0;
                }
                $apply[$i]["whx"]=$apply[$i]["fb_shijiweishui"]-$apply[$i]["yh"]-$apply[$i]["sqz"];
                if($yihenu){
                    $apply[$i]["yihe"]=1;
                    $apply[$i]["hexiao"]=$yihenu;
                }else{
                    $apply[$i]["yihe"]=0;
                    $apply[$i]["hexiao"]=[];
                }
                if($apply[$i]["fb_shijixiaoliang"]){
                    $apply[$i]["shijiroi"]=($apply[$i]["fb_shijixiaoliang"]-$apply[$i]["totalBenchmarkSales"])*$apply[$i]["profit"]/100/$apply[$i]["fb_shijitourufeiyong"];
                }else{
                    $apply[$i]["shijiroi"]=0;
                }
                $apply[$i]["yujiroi"]=($apply[$i]["totalExpectedSales"]-$apply[$i]["totalBenchmarkSales"])*$apply[$i]["profit"]/100/$apply[$i]["yjsjtrfy"];
               
            }
            $this->assign("apply",$apply);
            $this->assign("data",$data);
        return $this->fetch();
    }
}